<?php
Class ModelReportV2Buyer extends Model {

     /*
     * 获取基于商品的销售报表
     * 最近一周销售总数&总金额『 TQLW / TPLW 』
     * 最近四周销售总数&总金额『 TQLM / TPLM 』
     * 前年销售总数&总金额『 TQTY / TPTY 』
     * 毛利率（当年销售总价-总成本）/总价『 GPRTY 』
     * 当前库存总数&总成本『 SQN / SCN 』
     * 当前库存预计销售周期（当前库存/最近四周销量总数*7）【单位：天】『 SSC 』
     * 采购在途数量『 SIOW 』
     * 采购在途预计销售周期（采购在途数量/最近四周销量总数）【单位：周】『 SIOWSC 』
     * 原始表单样式由 @采销部蒋总 提供
     */
    public function getbuyer($inventqty){
        $ret = $inventqty;
        $m = date('Y-m-d', mktime(0,0,0,date('m')-1,1,date('Y')));
        $t = date('t',strtotime($m)); 
        $maps = array(
            'LD' => date('Ymd').'0000',
            'LMN' => date('Ym').'0000',
            'TY' => date('Y').'0000',
            'LM' => date('Ymd', strtotime('-4 week')),
            'SY'=>date('Ymd', mktime(0,0,0,date('m')-1,1,date('Y')))
        );
        $endsy = date('Ymd', mktime(0,0,0,date('m')-1,$t,date('Y')));
        $yesterday = date("Ymd",strtotime("-1 day")).'0000';
        foreach ($maps as $k => $value) {
            
            if ($k=='SY') {
                $sql = "SELECT SUM(OP.`quantity`) AS TQ".$k.",SUM(OP.`total`) AS TP".$k.",SUM(VP.`product_cost`*OP.`quantity`) AS TC".$k." , P.product_class1, C.`name` FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND `date_added` >'".$value."000000' AND `date_added` <'".$endsy."235959' ) GROUP BY P.product_class1";
                
            }else if ($k=='LD') {
                 $sql = "SELECT SUM(OP.`quantity`) AS TQ".$k.",SUM(OP.`total`) AS TP".$k.",SUM(VP.`product_cost`*OP.`quantity`) AS TC".$k." , P.product_class1, C.`name` FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND `date_added` <'".$value."000000' AND `date_added` >'".$yesterday."000000' ) GROUP BY P.product_class1";
            }else{
             $sql = "SELECT SUM(OP.`quantity`) AS TQ".$k.",SUM(OP.`total`) AS TP".$k.",SUM(VP.`product_cost`*OP.`quantity`) AS TC".$k." , P.product_class1, C.`name` FROM `order_product` AS OP LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = OP.`product_id` LEFT JOIN `product` AS P ON P.`product_id` = OP.`product_id` LEFT JOIN `category_description` AS C ON P.`product_class1` = C.category_id WHERE OP.`order_id` IN (SELECT `order_id` FROM `order` WHERE `order_status_id` <> 16 AND `date_added` >'".$value."000000') GROUP BY P.product_class1";
            }
            $query = $this->db->query($sql);
            foreach ($query->rows as $row) {
                $pclass = $row['product_class1'];
                if (!empty($pclass)) {
                    $ret[$pclass]['TQ'.$k] = $row['TQ'.$k];
                    $ret[$pclass]['TP'.$k] = round($row['TP'.$k], 2);
                    $ret[$pclass]['TPXL'.$k] = number_format(round($row['TP'.$k], 2));
                    $ret[$pclass]['TC'.$k] = round($row['TC'.$k], 2);
                    $ret[$pclass]['name'] = $row['name'];
                }
                
            }
        }
        foreach ($ret as $GPRTY=>$info) {
                if ((int)$info['TQTY'] < 1) {
                    continue;
                }
                $info['GPRTY'] = (round(($ret[$GPRTY]['TPTY']-$ret[$GPRTY]['TCTY'])/$ret[$GPRTY]['TPTY'], 4)*100).'%';
                $info['GPRLMN'] = (round(($ret[$GPRTY]['TPLMN']-$ret[$GPRTY]['TCLMN'])/$ret[$GPRTY]['TPLMN'], 4)*100).'%';
                $info['GPRSY'] = (round(($ret[$GPRTY]['TPSY']-$ret[$GPRTY]['TCSY'])/$ret[$GPRTY]['TPSY'], 4)*100).'%';

                $ret[$GPRTY]['SQN'] = $info['availableQty'];
                $info['SCN'] = $info['availableQty']*$info['cost'];
                if ($info['TQLM']) {
                    $info['SSC'] = round($ret[$GPRTY]['SQN']/$info['TQLM']*30, 2);
                }else{
                    $info['SSC'] = '--';
                }

                switch ($GPRTY) {
                    case '257':
                    case '258':
                    case '264':
                    case '266':

                       $arrn['YL'][] = $info;
                        break;
                    case '259':
                    case '263':
                    case '265':
                       $arrn['DM'][] = $info; 
                        break;
                    case '260':
                    case '267':
                       $arrn['ZDM'][] = $info; 
                        break;
                    case '744':
                    case '789':
                       $arrn['QT'][] = $info; 
                        break;
                    
                    default:
                        # code...
                        break;
                }
        }
       return $arrn;
        
    }

    public function getInvenTotal($pobuyer){
        $ret = $pobuyer;
        $sql = "SELECT SUM(I.`available_quantity`) as available_quantity ,SUM(I.`available_quantity`*VP.`product_cost`) as available_price , P.product_class1 FROM `inventory` AS I LEFT JOIN `product_option_value` AS O ON O.`product_code` = I.`product_code` LEFT JOIN `product` AS P ON P.`product_id` = O.`product_id` LEFT JOIN `vendor` AS VP ON VP.`vproduct_id` = P.`product_id` GROUP BY    P.product_class1"; 
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['product_class1'];
            if (!empty($pclass)) {
                $ret[$pclass]['availableQty'] = max(0, $row['available_quantity']);
                $ret[$pclass]['totalprice'] = max(0, $row['available_price']);
                $ret[$pclass]['totalpricexs'] = number_format(max(0, $row['available_price']));
            }
        }
        return $ret;
    }
    public  function getPobuyer(){
        $sql =  "SELECT po_product.product_code, SUM(po_product.price) AS price, p.product_class1, SUM(po_product.qty - po_product.delivered_qty ) AS buyqty FROM po_product LEFT JOIN po ON po_product.po_id = po.id LEFT JOIN product_option_value AS pv on pv.product_code = po_product.product_code LEFT JOIN product AS p  ON p.product_id = pv.product_id WHERE (po.`status` != 6 AND po.`status` != 9 AND po.`status` != 4 ) GROUP BY p.product_class1";
        $query = $this->db->query($sql);
        foreach ($query->rows as $row) {
            $pclass = $row['product_class1'];
            if (!empty($pclass)) {
                $ret[$pclass]['pobuyprice'] = max(0, $row['price']);
                $ret[$pclass]['pobuypricexs'] = number_format(max(0, $row['price']));
            }
        }
        return $ret;
    }


    
}
